package hms.ReservationsModule;

import java.sql.*;

import hms.database.*;

public class TestDB_noGUI {
	DAO db = new DAO("HMSDatabase","db_files\\", true);
	
	
	//overall method to print out columns for specified table 
	public static void printColumns(ResultSet rs) {
		ResultSetMetaData resMetaData;
		try {
			resMetaData = rs.getMetaData();
			int colCount = resMetaData.getColumnCount();
			for (int i = 1; i <= colCount; i++) {
				System.out.print(resMetaData.getColumnName(colCount) + "\t");
			}
			System.out.println();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public static void printReservations(ResultSet rs) throws SQLException{
		try {
			printColumns(rs);
			while (rs.next()) {
				System.out.println(rs.getObject("guest_ID") + "\t"
						+ rs.getObject("COFFEE_NAME") + "\t"
						+ rs.getObject("SUPPLIER_ID")
						);
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	
	
	public static void main(String[] args) {
		try {
			String dbName = "sample_database";
			String file_location = "c:\\sample_db\\"; // location of physical
														// database files, UNIX
														// and Windows file
														// separator acceptable

			// String file_location =""; // location relative of java program

			DAO db1 = null;
			db1 = new DAO(dbName, file_location, false); // not checking if
															// dbName exists
															// already

			System.out.print("Check the filesystem at " + file_location
					+ " for the following files:\n" + "\t\t" + dbName
					+ ".properties\n" + "\t\t" + dbName + ".script");

			System.out.println("");

			DAO db2 = null;
			db2 = new DAO(dbName, file_location, false); // not checking if
															// dbName exists
															// already

			
			boolean success = db1.open();
			System.out.println("Database open with: " + success);			

			System.out.println(db1
							.updateCommand("INSERT INTO guest(guest_ID,first_name, last_name) VALUES(15, 'ELTON', 'JOHN')"));
			System.out.println(db1
							.updateCommand("INSERT INTO guest(guest_ID, first_name, last_name) VALUES(20, 'PET SHOP', ' BOYS')"));
			ResultSet rs = db1.getResult("SELECT * FROM guest");
			printReservations(rs);

			System.out.println(db1
							.updateCommand("INSERT INTO guest(guest_ID, first_name) VALUES(25, 'WHAM')"));
			System.out.println(db1
							.updateCommand("INSERT INTO guest(guest_ID, guest_name) VALUES(30, 'BLONDIE')"));

			//open-close section #1
			db2.open();
			ResultSet rs1 = db2.getResult("SELECT * FROM guest");
			printReservations(rs1);

			System.out.println(db2
							.updateCommand("INSERT INTO reservations(reservation_ID, first_name, guest_ID) VALUES"
									+ "(13, 'Dark', 15),"
									+ "(23, 'Light', 20),"
									+ "(33, 'Medium', 25),"
									+ "(43, 'Apple', 30),"
									+ "(53, 'Orange', 15),"
									+ "(63, 'Banana', 20),"
									+ "(73, 'Mocha', 25),"
									+ "(83, 'Strong', 30),"
									+ "(93, 'Decaf', 15),"
									+ "(103, 'Folgers', 20)"));

			ResultSet rs2 = db1.getResult("SELECT * FROM reservation");
			db2.close();
			
			//open-close section #2
			db1.open();  // Have to reopen connection a sticking point
			ResultSet rs3 = db1.getResult("SELECT GUEST.guest_ID, " +
					"GUEST.first_name, RESERVATIONS.reservation_ID, " +
					"RESERVATIONS.reservation_NAME FROM RESERVATION INNER JOIN SUPPLIER ON RESERVATION.guest_ID = GUEST.guest_ID");

			db1.close();

			//open-close section #3
			db1.open();

			System.out.println(db1
							.updateCommand("INSERT INTO guest(guest_ID,first_name) VALUES(115, 'p Styx')"));
			System.out.println(db1
							.updateCommand("INSERT INTO guest(guest_ID,first_name) VALUES(120, 'p Chciago')"));
			ResultSet rs4 = db1.getResult("SELECT * FROM guest");
			printReservations(rs4);

			System.out.println(db1
							.updateCommand("INSERT INTO guest(guest_ID,first_name) VALUES(125, 'p Journey')"));
			System.out.println(db1
							.updateCommand("INSERT INTO guest(guest_ID,first_name) VALUES(130, 'p Spandu Ballet')"));

			ResultSet rs5 = db1.getResult("SELECT * FROM guest");
			printReservations(rs5);

			db1.close();
		} catch (SQLException ex) {
			ex.printStackTrace(System.out);
		}
	}
}
